package com.ctvit.syn.service;

import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.axis.utils.ArrayUtil;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ibatis.common.jdbc.logging.PreparedStatementLogProxy;


public class ToRemoteSynService extends SyncDataBaseService {

	private static final long serialVersionUID = 1L;
	public void test() throws Exception {
		ArrayList list = new ArrayList();
		list.add("1");
		boolean existsRecord=false;
//		existsRecord = recordExists("t_sync_test", new String[]{"f_sync_test_pk"}, list);

		System.out.println(existsRecord);
	}

	public void syncTableDate(SyncTableInfo tableName,String src,String dest) throws Exception {
		Connection connLocal = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			connLocal = getConnect_local();
			ResultSetMetaData metaData;
			List<String> columnNames;
			StringBuffer insertsql = new StringBuffer();          //准备要同步的数据的SQL
			insertsql.append("select * from ");
			insertsql.append(tableName.getTablename());
			log.debug("select before insert=" + insertsql);
			pstmt = connLocal.prepareStatement(insertsql.toString());

			rs = pstmt.executeQuery();                                   //执行SQL 获取数据
			metaData = rs.getMetaData();                              //获取列名的数据集
			int count = metaData.getColumnCount();
			columnNames = new ArrayList<String>();
			for (int i = 1; i <= count; i++) {
				columnNames.add(metaData.getColumnName(i));
			}
			saveOrUpdate(columnNames, rs, tableName, "insert", false,dest);//insert会跳过已经存在的记录
			rs.beforeFirst();
			saveOrUpdate(columnNames, rs, tableName, "update", false,dest);
		} catch (SQLException e) {
			connLocal.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(connLocal);
		}
	}

	public void insertTableData(SyncTableInfo tableName, Date date, Date now,String src,String dest) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
//		String sysid = getRemoteSysId();
		try {
//			Date lastSynDate = getLastSynDate(tableName.getTablename(), "insert");
			if (src.equals("local")){
				conn = getConnect_local();
			}else if (src.equals("remote")){
				conn = getConnect_remote();
			}else if (src.equals("notice")){
				conn = getConnect_notice();
			}
			if (date != null) {
				DateFormat formatWithTime = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
				ResultSetMetaData metaData = null;
				List<String> columnNames = null;
				StringBuffer insertsql = new StringBuffer();
				if (tableName.getTablename().equalsIgnoreCase("NOTICE_FILE")){
					insertsql.append("select distinct a.* from ");
				}else{
					insertsql.append("select * from ");
				}
				if (src.equals("notice")){
					insertsql.append("pansky."+tableName.getTablename());
				}else{
					insertsql.append(tableName.getTablename());
				}
				
				if (tableName.getTablename().equalsIgnoreCase("t_worderror")){
					pstmt = conn.prepareStatement(insertsql.toString());
				}else{
					if (tableName.getPkFieldValue()!=null&& tableName.getPkFieldValue().length>0){
						insertsql.append(" where z_req_id="+tableName.getPkFieldValue()[0]);
						pstmt = conn.prepareStatement(insertsql.toString());
					}else{
						if (tableName.getTablename().equalsIgnoreCase("V_NOTICE_MAIN_AVD")||tableName.getTablename().equalsIgnoreCase("T_NOTICE")) {
	//						insertsql.append(" where MADETIME > to_Date('" + formatWithTime.format(lastSynDate) + "','yyyy-mm-dd hh24:mi:ss')");
							insertsql.append(" where MADETIME > ?");
						}else if (tableName.getTablename().equalsIgnoreCase("NOTICE_FILE")) {
							insertsql.append(" a inner join T_NOTICE b on a.noticeid=b.NOTICEID where MADETIME > ?");
						}else if (tableName.getTablename().equalsIgnoreCase("zcdm_t_prorec")||tableName.getTablename().equalsIgnoreCase("zcdm_t_proglist")
								||tableName.getTablename().equalsIgnoreCase("zcdm_t_chanl")||tableName.getTablename().equalsIgnoreCase("zcdm_t_subj")) {
							insertsql.append(" where z_crtim > ?");
						}else{
							insertsql.append(" where CREATE_TIMESTAMP > ?");
						}
						pstmt = conn.prepareStatement(insertsql.toString());
						pstmt.setTimestamp(1, new Timestamp(date.getTime()));
					}
				}
				log.debug("select before insert=" + insertsql);
				rs = pstmt.executeQuery();
				metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				columnNames = new ArrayList<String>();
				for (int i = 1; i <= count; i++) {
					columnNames.add(metaData.getColumnName(i));
				}
				saveOrUpdate(columnNames, rs, tableName, "insert", false,dest);//
//				if (StringUtils.equalsIgnoreCase(sysid, "business")) {
//					saveOrUpdate(columnNames, rs, tableName, "insert", false,dest);//insert会跳过已经存在的记录
//					rs.first();
//					rs = pstmt.executeQuery();
//					saveOrUpdate(columnNames, rs, tableName, "update", false,dest);
//				} else {
//					saveOrUpdate(columnNames, rs, tableName, "insert", false,dest);//
//				}
				//
				if (tableName.getPkFieldValue()!=null&& tableName.getPkFieldValue().length>0){
				}else{
					updateTime(tableName.getSourceDb(),tableName.getDestDb(),tableName.getTablename(), now, "insert","");
				}
				conn.commit();
			} else {
				throw new Exception("date is  null!");
			}
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
		}
	}

	private void saveOrUpdate(List<String> columnNames, ResultSet rs, SyncTableInfo tableName, String op, boolean isDateTable,String dest) throws SQLException {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			if (dest.equals("local")){
				conn = getConnect_local();
			}else if (dest.equals("remote")){
				conn = getConnect_remote();
			}else if (dest.equals("notice")){
				conn = getConnect_notice();
			}
			conn.setAutoCommit(false);
			Object o = null;
			int sum = 0;
			String destTableName=tableName.getTablename().equals("V_NOTICE_MAIN_AVD")?"t_notice":tableName.getTablename();
			if ("insert".equals(op)) {
				StringBuffer tosql = new StringBuffer("insert into ");
				tosql.append(destTableName);
				tosql.append("(");
				for (int i = 0; i < columnNames.size(); i++) {
					if (i == columnNames.size() - 1) {
						tosql.append(columnNames.get(i) + ")");
					} else {
						tosql.append(columnNames.get(i) + ",");
					}
				}
				tosql.append(" values (");
				for (int i = 0; i < columnNames.size(); i++) {
					if (i == columnNames.size() - 1) {
						tosql.append("?)");
					} else {
						tosql.append("?,");
					}
				}
//				pstmt=PreparedStatementLogProxy.newInstance(pstmt, tosql.toString());
				pstmt = conn.prepareStatement(tosql.toString());
				String pkField[] = tableName.getPkFieldName();
				String noticeId="";
				String filename="";String fileNameEncode="";
				while (rs.next()) {
					ArrayList valueList = new ArrayList();
					if (!ArrayUtils.isEmpty(pkField)){
						for (String f_pkField : pkField) {
							valueList.add(rs.getString(f_pkField));
						}
					}
//					String pkField_1 = pkField[0];
//					String pkString_1 = rs.getString(pkField_1);
//					if (recordExists(tableName.getTablename(), pkField_1, pkString_1)) continue;
					if (!ArrayUtils.isEmpty(pkField)&&recordExists(destTableName, pkField, valueList,dest)) continue;
					sum++;
					for (int i = 0; i < columnNames.size(); i++) {
						if (!isDateTable) {
							if ("CREATEDTIMESTAMP".equalsIgnoreCase(columnNames.get(i))) {
								o = rs.getTimestamp("CREATE_TIMESTAMP");
							} else {
								o = rs.getObject(columnNames.get(i));
								if (o instanceof java.sql.Date) {
									o = rs.getTimestamp(columnNames.get(i));
								}
							}
						} else {
							o = rs.getObject(columnNames.get(i));
							if (o instanceof java.sql.Date) {
								o = rs.getTimestamp(columnNames.get(i));
							}
						}
						if (o instanceof Clob) {
							pstmt.setString(i + 1, rs.getString(columnNames.get(i)));
						} else {
							pstmt.setObject(i + 1, o);
						}
					}
					pstmt.addBatch();
					if (sum % BATCH_SIZE == 0) {
						pstmt.executeBatch();

					}
					if (StringUtils.equalsIgnoreCase(tableName.getTablename(), "NOTICE_FILE")){
						noticeId=rs.getString("noticeid");
						filename=rs.getString("filename");
						try {
							fileNameEncode=URLEncoder.encode(filename, "utf8");
//							fileNameEncode=fileNameEncode.replaceAll("\\+","%20");
						} catch (UnsupportedEncodingException e) {
							e.printStackTrace();
						}
						String urlString="http://webapp.corp.cctv.com/appattach/notice/noticeedit/attached/";
//						urlString="http://localhost:8080/messageCenter/attached/";
						FileTransfer.httpTranster(urlString+noticeId+"/"+fileNameEncode, filename,noticeId,"");
					}
				}
			} else if ("update".equals(op) && isDateTable) {
				StringBuffer tosql = new StringBuffer("update ");
				tosql.append(destTableName);
				tosql.append(" set ");
				for (String pk : tableName.getPkFieldName()) {
					columnNames.remove(pk);
				}
				for (int i = 0; i < columnNames.size(); i++) {
					if (i == columnNames.size() - 1) {
						tosql.append(columnNames.get(i) + " = ? ");
					} else {
						tosql.append(columnNames.get(i) + " = ?,");
					}
				}

				tosql.append("where ");
				for (int i = 0; i < tableName.getPkFieldName().length; i++) {
					if (i == tableName.getPkFieldName().length - 1) {
						tosql.append(tableName.getPkFieldName()[i] + " = ?");
					} else {
						tosql.append(tableName.getPkFieldName()[i] + " = ? and ");
					}
				}
				pstmt = conn.prepareStatement(tosql.toString());
//				pstmt=PreparedStatementLogProxy.newInstance(pstmt, tosql.toString());
				while (rs.next()) {
					sum++;
					for (int i = 0; i < columnNames.size(); i++) {
						o = rs.getObject(columnNames.get(i));
						if (o != null) {
							if (o instanceof java.sql.Date) {
								pstmt.setObject(i + 1, rs.getTimestamp(columnNames.get(i)));
							} else {
								pstmt.setObject(i + 1, rs.getObject(columnNames.get(i)));
							}
						} else {
							pstmt.setObject(i + 1, o);
						}
					}
					for (int i = 0; i < tableName.getPkFieldName().length; i++) {
						pstmt.setObject(columnNames.size() + i + 1, rs.getObject(tableName.getPkFieldName()[i]));
					}
					pstmt.addBatch();
					if (sum % BATCH_SIZE == 0) {
						pstmt.executeBatch();
					}
				}
			} else if ("update".equals(op) && !isDateTable) {
				StringBuffer tosql = new StringBuffer("update ");
				tosql.append(destTableName);
				tosql.append(" set ");
				for (int i = 0; i < tableName.getPkFieldName().length; i++) {
					columnNames.remove(tableName.getPkFieldName()[i]);
				}
				for (int i = 0; i < columnNames.size(); i++) {
					if (i == columnNames.size() - 1) {
						tosql.append(columnNames.get(i) + " = ? ");
					} else {
						tosql.append(columnNames.get(i) + " = ?,");
					}
				}
				tosql.append("where ");
				for (int i = 0; i < tableName.getPkFieldName().length; i++) {
					if (i == tableName.getPkFieldName().length - 1) {
						tosql.append(tableName.getPkFieldName()[i] + " = ?");
					} else {
						tosql.append(tableName.getPkFieldName()[i] + " = ? and ");
					}
				}
				pstmt = conn.prepareStatement(tosql.toString());

				while (rs.next()) {
					sum++;
					for (int i = 0; i < columnNames.size(); i++) {
						if ("LASTMODIFIEDTIMESTAMP".equalsIgnoreCase(columnNames.get(i))) {
							o = rs.getTimestamp("MODIFY_TIMESTAMP");
						} else {
							o = rs.getObject(columnNames.get(i));
							if (o instanceof java.sql.Date) {
								o = rs.getTimestamp(columnNames.get(i));
							}
						}
						if (o instanceof Clob) {
							pstmt.setString(i + 1, rs.getString(columnNames.get(i)));
						} else {
							pstmt.setObject(i + 1, o);
						}
					}
					for (int i = 0; i < tableName.getPkFieldName().length; i++) {
						pstmt.setObject(columnNames.size() + i + 1, rs.getObject(tableName.getPkFieldName()[i]));
					}
					pstmt.addBatch();
					if (sum % BATCH_SIZE == 0) {
						pstmt.executeBatch();
					}
				}
			}
			pstmt.executeBatch();
			conn.commit();
			log.info("from "+tableName.getSourceDb()+" to "+tableName.getDestDb()+"-----"+destTableName+":"+op + ":" + "commit rows= " + sum);
		} catch (SQLException e) {
			JDBCUtil.rollback(conn);
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
		}
	}
	/**
	 * 删除目标系统的满足主键的记录
	 * @param tableName
	 * @param date
	 * @param now
	 * @param src
	 * @param dest
	 * @throws Exception
	 */
	public void delTableData(SyncTableInfo tableName, Date date, Date now,String src,String dest) throws Exception {
		Connection conn = null;
		Connection connLocal=null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			connLocal=getConnect_local();
			if (dest.equals("local")){
				conn = getConnect_local();
			}else if (dest.equals("remote")){
				conn = getConnect_remote();
			}else if (dest.equals("notice")){
				conn = getConnect_notice();
			}
			date=new Date();
			if (date != null) {
				String sql = "select PKVALUE from QRTZ_SYNC_TIMESTAMP where name = '" + tableName.getTablename().toUpperCase()+ "' and SOURCESYS='"+src.toUpperCase()+"' and DESTSYS='"+dest.toUpperCase()+"'";;
				pstmt = connLocal.prepareStatement(sql);
				rs = pstmt.executeQuery();
				String pkValues="";
				while (rs.next()) {
					pkValues = rs.getString("PKVALUE");
				}
				if (StringUtils.isEmpty(pkValues)) pkValues="";
				String[] pkvalueArr=pkValues.split(",");
				if (pkvalueArr!=null){
					StringBuffer deletesql = new StringBuffer();
					deletesql.append("delete  from ");
					deletesql.append(tableName.getTablename());
					deletesql.append("  where ");
					String[] pkField=tableName.getPkFieldName();
					boolean needDel=false;
					for (int i = 0; i < pkvalueArr.length; i++) {
						if (StringUtils.isEmpty(pkvalueArr[i])) continue;
						needDel=true;
						int lastValueIndex=0;
						for (int ii = 0; ii < pkField.length; ii++) {
							lastValueIndex = pkField.length - 1;
							if (ii == lastValueIndex) {
								deletesql.append(pkField[ii] + " = '"+pkvalueArr[i]+"' or ");
							} else {
//								deletesql.append(pkField[ii] + " = "+pkvalueArr[i]+" and ");
							}
						}
					}
					if (needDel){
						String sqlString=deletesql.toString();
						pstmt = conn.prepareStatement(sqlString.substring(0,sqlString.length()-3));
						int rt = pstmt.executeUpdate();
					}
				}
				//
				updateTime(tableName.getSourceDb(),tableName.getDestDb(),tableName.getTablename(), now, "delete",pkValues);
				conn.commit();
			} else {
				throw new Exception("date is not null!");
			}
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
			JDBCUtil.closeConnection(connLocal);
		}
	}
	public void updateTableData(SyncTableInfo tableName, Date date, Date now,String src,String dest) throws Exception {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			if (src.equals("local")){
				conn = getConnect_local();
			}else if (src.equals("remote")){
				conn = getConnect_remote();
			}else if (src.equals("notice")){
				conn = getConnect_notice();
			}
			if (date != null) {
				ResultSetMetaData metaData = null;
				List<String> columnNames = null;
				StringBuffer updatesql = new StringBuffer();
				updatesql.append("select * from ");
				updatesql.append(tableName.getTablename());
				if (tableName.getTablename().equalsIgnoreCase("zcdm_t_prorec")||tableName.getTablename().equalsIgnoreCase("zcdm_t_proglist")
					||tableName.getTablename().equalsIgnoreCase("zcdm_t_subj")) {
					updatesql.append(" where Z_UPTIM > ? and Z_UPTIM <= ?");
				}else{
					updatesql.append(" where MODIFY_TIMESTAMP > ? and MODIFY_TIMESTAMP <= ?");
				}
				pstmt = conn.prepareStatement(updatesql.toString());
				pstmt.setTimestamp(1, new Timestamp(date.getTime()));
				pstmt.setTimestamp(2, new Timestamp(now.getTime()));
				rs = pstmt.executeQuery();
				metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				columnNames = new ArrayList<String>();
				for (int i = 1; i <= count; i++) {
					columnNames.add(metaData.getColumnName(i));
				}
				saveOrUpdate(columnNames, rs, tableName, "update", true,dest);
				//
				updateTime(tableName.getSourceDb(),tableName.getDestDb(),tableName.getTablename(), now, "update","");
				conn.commit();
			} else {
				throw new Exception("date is not null!");
			}
		} catch (SQLException e) {
			conn.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(conn);
		}
	}

	private void printDebugInfo(String tablename, String op, int count) {
		log.info(op + " " + tablename + " " + count);
	}
}
